package cn.edu.zjusc.ExcelGenerator;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import cn.edu.zjusc.ExcelModel.InterviewGroupExamInfo;
import cn.edu.zjusc.ExcelModel.StudentGroupInfo;

public class InterviewGroupExamGenerator {
	/**
	 * The spreadsheet to read in
	 */
	private File inputWorkbook;
	/**
	 * The spreadsheet to output
	 */
	private File outputWorkbook;
	
	InputStream inp;
	FileOutputStream fileOut;
	private SimpleDateFormat mDateFormat = new SimpleDateFormat("yyyy-MM-dd");
	
	Workbook wb;
	
	public void modify(String input, String output,List studentList,String theYear,String theSeason,String examPlace,int interviewGroupExamId,Date startTime) throws InvalidFormatException, IOException{
		inp = new FileInputStream(input);
		 
		 wb = WorkbookFactory.create(inp);
		 
		 completeData(input,output,studentList,theYear,theSeason,examPlace,interviewGroupExamId,startTime);
		 fileOut = new FileOutputStream(output);
		    wb.write(fileOut);
		    fileOut.close();
	}
	
	private void completeData(String input, String output, List<InterviewGroupExamInfo> studentList,String theYear,String theSeason,String examPlace,int interviewGroupExamId,Date startTime){
		//样式-带边框，居中
		CellStyle style1 = wb.createCellStyle();
		style1.setBorderBottom(CellStyle.BORDER_THIN); 
		style1.setBorderLeft(CellStyle.BORDER_THIN);
		style1.setBorderRight(CellStyle.BORDER_THIN);
		style1.setAlignment(CellStyle.ALIGN_CENTER); //ALIGN_CENTER
		style1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		
		System.out.println("----------------------开始生成表格");
		int listSize=studentList.size();
		CreationHelper createHelper = wb.getCreationHelper();
		//对sheet0-面试分组
		Sheet sheet0 = wb.getSheetAt(0);
		Row row01=sheet0.getRow(0);
		//int year = studentList
		//修改sheet0表头
		if (theSeason.equals("0")) {
			theSeason="春季";
		} else if(theSeason.equals("1")){
			theSeason="秋季";
		}
		if (examPlace.equals("0")) {
			examPlace="杭州";
		} else if(examPlace.equals("1")) {
			examPlace="宁波";
		}
		row01.createCell(0).setCellValue("浙江大学"+theYear+"年"+theSeason+"软件工程硕士入学考试面试分组");
		wb.getSheetAt(1).getRow(0).getCell(0).setCellValue("浙江大学"+theYear+"年"+theSeason+"软件工程硕士入学考试考生基本信息表");
		wb.getSheetAt(2).getRow(0).getCell(0).setCellValue("浙江大学"+theYear+"年"+theSeason+"攻读软件工程硕士专业学位入学考试面试登分表");
		wb.getSheetAt(3).getRow(0).getCell(0).setCellValue("浙江大学"+theYear+"年"+theSeason+"攻读软件工程硕士专业学位入学考试面试登分汇总表");
		
		wb.getSheetAt(0).getRow(1).getCell(0).setCellValue(examPlace+" 第"+interviewGroupExamId+"组 地点:");
		wb.getSheetAt(0).getRow(2).getCell(0).setCellValue("时间："+startTime.toString().substring(0, 16));
		
		wb.getSheetAt(1).getRow(1).getCell(0).setCellValue(examPlace+" 第"+interviewGroupExamId+"组 地点:    "+"时间："+startTime.toString().substring(0, 16));
		wb.getSheetAt(2).getRow(1).getCell(0).setCellValue(examPlace+" 第"+interviewGroupExamId+"组 地点:    "+"时间："+startTime.toString().substring(0, 16));
		wb.getSheetAt(3).getRow(1).getCell(0).setCellValue(examPlace+" 第"+interviewGroupExamId+"组 地点:    "+"时间："+startTime.toString().substring(0, 16));
		//sheet0.setDefaultRowHeightInPoints(30);
		//循环创建cell，添加数据
		for(int i=0;i<listSize;i++){
			Row row = sheet0.createRow((short)(i+4));
			row.setHeightInPoints(30);
			row.createCell(0).setCellValue(i+1);
			row.getCell(0).setCellStyle(style1);
			row.createCell(1).setCellValue(studentList.get(i).getAdmissionTicketID());
			row.getCell(1).setCellStyle(style1);
			row.createCell(2).setCellValue(studentList.get(i).getName());
			row.getCell(2).setCellStyle(style1);
		}
		
		//对sheet1-基本信息
		Sheet sheet1 = wb.getSheetAt(1);
		//循环创建cell，添加数据
		for(int i=0;i<listSize;i++){
			Row row = sheet1.createRow((short)(i+3));
			row.setHeightInPoints(30);
			row.createCell(0).setCellValue(i+1);
			row.getCell(0).setCellStyle(style1);
			row.createCell(1).setCellValue(studentList.get(i).getAdmissionTicketID());
			row.getCell(1).setCellStyle(style1);
			row.createCell(2).setCellValue(studentList.get(i).getName());
			row.getCell(2).setCellStyle(style1);
			row.createCell(3).setCellValue(studentList.get(i).getMajor());
			row.getCell(3).setCellStyle(style1);
			row.createCell(4).setCellValue(studentList.get(i).getUnGrdtSchool());
			row.getCell(4).setCellStyle(style1);
			row.createCell(5).setCellValue(studentList.get(i).getUnGrdtMajor());
			row.getCell(5).setCellStyle(style1);
		}
		
		//对sheet2-面试登分表
		Sheet sheet2 = wb.getSheetAt(2);
		//循环创建cell，添加数据
		for(int i=0;i<listSize;i++){
			Row row = sheet2.createRow((short)(i+3));
			row.setHeightInPoints(30);
			row.createCell(0).setCellValue(i+1);
			row.getCell(0).setCellStyle(style1);
			row.createCell(1).setCellValue(studentList.get(i).getAdmissionTicketID());
			row.getCell(1).setCellStyle(style1);
			row.createCell(2).setCellValue(studentList.get(i).getName());
			row.getCell(2).setCellStyle(style1);
			row.createCell(3).setCellValue(studentList.get(i).getMajor());
			row.getCell(3).setCellStyle(style1);
			row.createCell(4).setCellStyle(style1);
			row.createCell(5).setCellStyle(style1);
			row.createCell(6).setCellStyle(style1);
			row.createCell(7).setCellStyle(style1);
			row.createCell(8).setCellStyle(style1);
		}
		//对sheet3-面试登分汇总表
		Sheet sheet3 = wb.getSheetAt(3);
		//循环创建cell，添加数据
		for(int i=0;i<listSize;i++){
			Row row = sheet3.createRow((short)(i+3));
			row.setHeightInPoints(30);
			row.createCell(0).setCellValue(i+1);
			row.getCell(0).setCellStyle(style1);
			row.createCell(1).setCellValue(studentList.get(i).getAdmissionTicketID());
			row.getCell(1).setCellStyle(style1);
			row.createCell(2).setCellValue(studentList.get(i).getName());
			row.getCell(2).setCellStyle(style1);
			row.createCell(3).setCellValue(studentList.get(i).getMajor());
			row.getCell(3).setCellStyle(style1);
			row.createCell(4).setCellStyle(style1);
			row.createCell(5).setCellStyle(style1);
			row.createCell(6).setCellStyle(style1);
			row.createCell(7).setCellStyle(style1);
			row.createCell(8).setCellStyle(style1);
		}
	}
	
	/**
	 * @param args
	 * @throws IOException 
	 * @throws InvalidFormatException 
	 */
//	public static void main(String[] args) throws InvalidFormatException, IOException {
//		// TODO Auto-generated method stub
//		InterviewGroupExamGenerator interviewGroupExamGenerator=new InterviewGroupExamGenerator();
//		List<InterviewGroupExamInfo> studentList=new ArrayList<InterviewGroupExamInfo>();
//		InterviewGroupExamInfo interviewGroupExamInfo=new InterviewGroupExamInfo("2121212", "lb", "eng", "浙大", "数学分析");
//		studentList.add(interviewGroupExamInfo);
//		InterviewGroupExamInfo interviewGroupExamInfo1=new InterviewGroupExamInfo("2121212", "lb", "eng", "浙大", "数学分析");
//		studentList.add(interviewGroupExamInfo1);
//		InterviewGroupExamInfo interviewGroupExamInfo2=new InterviewGroupExamInfo("2121212", "lb", "eng", "浙大", "数学分析");
//		studentList.add(interviewGroupExamInfo2);
//		InterviewGroupExamInfo interviewGroupExamInfo3=new InterviewGroupExamInfo("2121212", "lb", "eng", "浙大", "数学分析");
//		studentList.add(interviewGroupExamInfo3);
//		InterviewGroupExamInfo interviewGroupExamInfo4=new InterviewGroupExamInfo("2121212", "lb", "eng", "浙大", "数学分析");
//		studentList.add(interviewGroupExamInfo4);
//		System.out.println("----------------------开始");
//		interviewGroupExamGenerator.modify("./WebContent/interviewgroupexam.xls", "./WebContent/interviewgroupexam.xlsOK.xls", studentList);
//		System.out.println("----------------------over");
//	}

}
